package com.glsc.ngateway.common.base.repo.mysql.gateway.other;

import com.glsc.ngateway.common.base.domain.mysql.gateway.other.ProductRelation;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Repository
public interface ProductRelationRepository extends JpaRepository<ProductRelation,Integer>, JpaSpecificationExecutor<ProductRelation> {

    //根据prodIdList查询List<ProductRelation>
    @Query(value = "select * from t_am_product_relation where prod_id in (:prodIdList) and deleted = 'N'", nativeQuery = true)
    List<ProductRelation> findByProdIdList(@Param("prodIdList") List<Integer> prodIdList);

    //按照产品IDList查询有效的List<ProductRelation>
    @Query(value = "select * from t_am_product_relation where  relation_id IN( select relation_id from v_am_invest_manager_assistant where prod_id IN(:prodIdList) and (:relationType is null or relation_type = :relationType)) ", nativeQuery = true)
    List<ProductRelation> findInvestManagerAssistantListByProdIdList(List<Integer> prodIdList,String relationType);

    @Query(value = "SELECT PR from ProductRelation PR where prodId=:prodId and relationType='INVEST_MANAGER' and deleted = 'N'", nativeQuery = false)
    Page<ProductRelation> findInvestManagerPageOfProdId(@Param("prodId") Integer prodId, Pageable pageable);

    /**
     * 根据productIdList和relatedIdList批量取消投资经理
     * @param prodIdList
     * @param oldInvestManagerIdList
     * @return
     */
    @Transactional
    @Modifying(clearAutomatically=true, flushAutomatically = true)
    @Query(value = "update t_am_product_relation set invest_manager_effective_status = 'N',effective_end_date=:effectiveEndDate where prod_id IN(:prodIdList) and related_id in(:oldInvestManagerIdList)", nativeQuery = true)
    Integer cancelInvestManagerByProdIdListAndOldInvestManagerIdList(@Param("prodIdList") List<Integer> prodIdList, @Param("oldInvestManagerIdList") List<String> oldInvestManagerIdList,@Param("effectiveEndDate") String effectiveEndDate);

    @Query(value = "select * from t_am_product_relation a where a.prod_id = :prodId and deleted = 'N' and a.related_id  = :relatedId and (:effective_date between ifnull(a.effective_date,'2020-01-01' ) and ifnull(a.effective_end_date,'2099-12-31'))", nativeQuery = true)
    List<ProductRelation> findAllByProdIdAndRelatedId(Integer prodId,String relatedId,String effective_date);

    @Transactional
    @Modifying
    @Query(value = "update t_am_product_relation set effective_date = :prodStartDate where prod_id = :prodId and (effective_date is null or effective_date = '') and relation_type in(:relationTypeList)", nativeQuery = true)
    Integer updateEffectiveDateByProdId(@Param("prodId")Integer prodId,@Param("prodStartDate")String prodStartDate,@Param("relationTypeList")List<String> relationTypeList);

    //根据prodId和投资经理列表信息查询List<ProductRelation>
    @Query(value = "select * from t_am_product_relation where prod_id = :prodId and relation_type in(:relationTypeList) and deleted = :deleted", nativeQuery = true)
    List<ProductRelation> findListByProdIdAndRelationTypeList(@Param("prodId") Integer prodId, @Param("relationTypeList") List<String> relationTypeList,@Param("deleted") String deleted);

    @Query(value = "select * from t_am_product_relation where series_id = :seriesId and relation_type in(:relationTypeList) and deleted = :deleted", nativeQuery = true)
    List<ProductRelation> findListBySeriesIdAndRelationTypeList(@Param("seriesId") Integer seriesId, @Param("relationTypeList") List<String> relationTypeList,@Param("deleted") String deleted);

    //根据产品ID列表查询产品经理List<ProductRelation>
    @Query(value = "select * from t_am_product_relation where  relation_id IN( select relation_id from v_am_prod_manager where prod_id IN(:prodIdList)) ", nativeQuery = true)
    List<ProductRelation> findProdManagerListByProdIdList(List<Integer> prodIdList);

    @Query(value = "select a.* from t_am_product_relation a left join t_am_product b on a.prod_id = b.prod_id where a.relation_type = 'INVEST_MANAGER' and a.deleted = 'N' and b.prod_deleted='N' and a.prod_id in (:prodIdList)", nativeQuery = true)
    List<ProductRelation> findInvestManagerListByProdIdList(List<Integer> prodIdList);
    @Query(value = "select a.* from t_am_product_relation a left join t_am_prod_series b on a.series_id = b.series_id where a.relation_type = 'INVEST_MANAGER' and a.deleted = 'N' and b.deleted='N' and a.series_id in (:seriesIdList)", nativeQuery = true)
    List<ProductRelation> findInvestManagerListBySeriesIdList(List<Integer> seriesIdList);
    @Query(value = "select a.* from t_am_product_relation a left join t_am_prod_series b on a.series_id = b.series_id where a.relation_type = 'PRODUCT_MANAGER' and a.deleted = 'N' and b.deleted='N' and a.series_id in (:seriesIdList)", nativeQuery = true)
    List<ProductRelation> findProdManagerListBySeriesIdList(List<Integer> seriesIdList);
    @Query(value = "select a.* from t_am_product_relation a left join t_am_prod_series b on a.series_id = b.series_id where a.relation_type = 'MARKET_MANAGER' and a.deleted = 'N' and b.deleted='N' and a.series_id in (:seriesIdList)", nativeQuery = true)
    List<ProductRelation> findMarketManagerListBySeriesIdList(List<Integer> seriesIdList);
    //根据产品ID列表查询市场经理List<ProductRelation>
    @Query(value = "select * from t_am_product_relation where  relation_id IN( select relation_id from v_am_market_manager where prod_id IN(:prodIdList)) ", nativeQuery = true)
    List<ProductRelation> findMarketManagerListByProdIdList(List<Integer> prodIdList);

    //根据产品ID列表查询运营经理List<ProductRelation>
    @Query(value = "select * from t_am_product_relation where  relation_id IN( select relation_id from v_am_operation_manager where prod_id IN(:prodIdList)) ", nativeQuery = true)
    List<ProductRelation> findOperationManagerListByProdIdList(List<Integer> prodIdList);

    //根据remark和relationType查询List<ProductRelation>
    @Query(value = "select * from t_am_product_relation where remark in(:remarkList) and relation_type = :relationType and (sysdate() between ifnull(`effective_date`, '2010-01-01') and ifnull(`effective_end_date`, '2099-12-31')) and deleted = 'N'", nativeQuery = true)
    List<ProductRelation> findByRemarkAndRelationType(@Param("remarkList") List<String> remarkList,@Param("relationType") String relationType);

    List<ProductRelation> findAllByKeyIdAndRelationTypeInAndDeleted(Integer keyId,List<String> relationTypeList,String deleted);
    List<ProductRelation> findAllBySeriesIdAndRelationTypeInAndDeleted(Integer seriesId,List<String> relationTypeList,String deleted);

    List<ProductRelation> findAllByKeyIdInAndDeleted(List<Integer> keyIdList,String deleted);

    @Query(value = "select * from t_am_product_relation where prod_id = :prodId and relation_type = :relationType and deleted = 'N' and related_id = :relatedId  " +
            "and (date_format(sysdate(), '%Y-%m-%d') < if((ifnull(effective_date, '') = ''), '2010-01-01', effective_date))", nativeQuery = true)
    List<ProductRelation> findListByProdIdAndRelationTypeListAndRelatedId(Integer prodId,String relationType, String relatedId);
}
